package com.quanjiaolian.login.serviceimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.ResourceBundle;

import org.apache.log4j.Logger;

import com.quanjiaolian.DB.DataBase;
import com.quanjiaolian.login.service.LoginService;
import com.quanjiaolian.tools.Utils;

public class LoginServiceImpl implements LoginService {
	
	/**
	 * 声明Log4j变量
	 */
	public static ResourceBundle resourceBundle = null;
	static Logger logger = Logger.getLogger(LoginServiceImpl.class.getName());
	
	DataBase db = new DataBase();
	
	 /**
     * 声明数据库连接实例
     */
	//定义一个Connection 用来连接数据库 
	Connection conn=null;  
    //声明PreparedStatement实例
    PreparedStatement pstmt = null;
    //用于存放返回结果的数据集
    ResultSet rs = null;
	
	@SuppressWarnings("static-access")
	public int check(String msisdn, String passwd,int type) {
		String sql = "";
		int i = 0;
		conn = db.getconn();
		if(type == 0){
			sql = "select count(*) from user_info t where t.msisdn='"+msisdn+"' and t.passwd='"+passwd+"' and t.userstat = 0";
		}else{
			sql = "select count(*) from ins_info t where t.ins_msisdn='"+msisdn+"' and t.ins_passwd='"+passwd+"' and t.ins_stat = 0";
			i = 1;
		}
		System.out.println("check user sql is : "+sql);
		try {
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			rs.next();
			i = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			db.close(conn,pstmt,rs);
		}
		return i;
	}
	
	@SuppressWarnings("static-access")
	public Map<String,Object> queryUserInfoById(String msisdn,int type){
		String sql = "";
		Map<String,Object> map = new HashMap<String,Object>();
		conn = db.getconn();
		if(type == 0){
			sql = "select t.userid,t.msisdn,t.passwd,t.username,t.address,t.userpic_path,t.sex from user_info t where t.msisdn='"+msisdn+"'";
		}else{
			sql = "select t.ins_id,t.ins_msisdn,t.ins_passwd,t.ins_name,t.ins_addr,t.inspic_path,t.ins_sex from ins_info t where t.ins_msisdn='"+msisdn+"'";
		}
		System.out.println("check user sql is : "+sql);
		try {
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			rs.next();
			map.put("userid", rs.getInt(1));
			map.put("msisdn", rs.getString(2));
			map.put("passwd", new String(Utils.decode(rs.getString(3))));
			map.put("username", rs.getString(4));
			map.put("address", rs.getString(5));
			map.put("userpic", rs.getString(6));
			map.put("sex", rs.getInt(7));
			map.put("usertype",type);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			db.close(conn,pstmt,rs);
		}
		return map;
	}
	
	@SuppressWarnings("static-access")
	public int isRegister(String msisdn,String user_type){
		int i = 0;
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(*) FROM user_info ui WHERE ui.msisdn = ? ");
		sql.append("UNION ALL ");
		sql.append("SELECT COUNT(*) FROM ins_info ii WHERE ii.ins_msisdn = ?");
		
		/*if("0".equals(user_type)){
			sql = "SELECT COUNT(*) FROM user_info ui WHERE ui.msisdn = ?";
		}else{
			sql = "SELECT COUNT(*) FROM ins_info ii WHERE ii.ins_msisdn = ?";
		}*/
		conn = db.getconn();
		try {
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setString(1,msisdn);
			pstmt.setString(2,msisdn);
			rs = pstmt.executeQuery();
			rs.next();
			int j = rs.getInt(1);
			logger.info("查询手机号： "+msisdn+" 在用户表中的记录数为： "+j);
			rs.next();
			int k = rs.getInt(1);
			logger.info("查询手机号： "+msisdn+" 在教练表中的记录数为： "+k);
			i = j+k;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.close(conn, pstmt, rs);
		}
		return i;
	}
	
	@SuppressWarnings("static-access")
	public int insertNewUser(String msisdn,String passwd,String user_type,String user_name){
		int i = 0;
		String sql = "";
		if("0".equals(user_type)){
			if(user_name == null || "".equals(user_name)){
				user_name = "用户"+Utils.makeCode();
			}
			sql = "insert into user_info (msisdn,passwd,username,record_date,userstat) VALUES(?,?,?,?,0)";
		}else{
			if(user_name == null || "".equals(user_name)){
				user_name = "教练"+Utils.makeCode();
			}
			sql = "INSERT INTO ins_info (ins_msisdn,ins_passwd,ins_name,record_date,ins_stat) VALUES(?,?,?,?,0)";
		}
		conn = db.getconn();
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,msisdn);
			logger.info("insertNewUser msisdn is:"+msisdn);
			pstmt.setString(2,Utils.encode(passwd.getBytes()));
			logger.info("insertNewUser passwd is:"+Utils.encode(passwd.getBytes()));
			pstmt.setString(3,user_name);
			logger.info("insertNewUser user_name is:"+user_name);
			pstmt.setTimestamp(4,new java.sql.Timestamp(System.currentTimeMillis()));
			i = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return i;
		
	}
	
	@SuppressWarnings("static-access")
	public int updateUserPasswd(String msisdn,String passwd,String user_type){
		int i = 0;
		String sql = "";
		if("0".equals(user_type)){
			sql = "update user_info set passwd = ? where msisdn = ?";
		}else{
			sql = "update ins_info set ins_passwd = ? where ins_msisdn = ?";
		}
		conn = db.getconn();
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,Utils.encode(passwd.getBytes()));
			pstmt.setString(2,msisdn);
			logger.info("正在将用户号码："+msisdn+" 角色为："+user_type+" 的密码重置为："+Utils.encode(passwd.getBytes()));
			i = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}
	
	public static void main(String[] args) {
		LoginServiceImpl ls = new LoginServiceImpl();
		System.out.println(ls.check("15101032667","YWRtaW4=",1));
	}

}
